package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.xmy.cultivate.entity.CourseSchedulingDetail;
import com.xmy.cultivate.entity.RecordCourse;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.RepairLesson;
import com.xmy.cultivate.entity.views.ArrivalRate;
import com.xmy.cultivate.entity.views.RecordCourseTiny;
import com.xmy.cultivate.entity.views.StudentClassLogRep;
import com.xmy.cultivate.respons.ClassesNum;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 记止课 Mapper 接口
 * </p>
 *
 * @author baomidou
 * @since 2022-08-10
 */
@Mapper
public interface RecordCourseMapper extends BaseMapper<RecordCourse> {


    @Select("select * from record_course where scheduling_detail_id = #{schedulingId} and student_id = #{studentId} limit 0,1")
    public abstract RecordCourseTiny getListForSchedulingIdAndStuId(@Param("schedulingId") Long schedulingId, @Param("studentId") Long studentId);

    @Select("SELECT * FROM course_scheduling_detail ${ew.customSqlSegment}")
    public abstract IPage<CourseSchedulingDetail> findAll(IPage<?> page, @Param(Constants.WRAPPER) QueryWrapper queryWrapper);

    @Select("select * from record_course ${ew.customSqlSegment}")
    public abstract List<RecordCourse> getRecordCourseList(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);


    @Select("SELECT COUNT(id) FROM record_course WHERE deleted = 0 and end_date_time > NOW() AND student_id = ${studentId} AND subjects_id = ${subjectsId} AND course_type IN (4) LIMIT 1")
    public abstract Integer getRecordCourseByDate(@Param("subjectsId") Long subjectsId, @Param("studentId") Long studentId);


    @Select("SELECT t1.*,t1.id AS scheduling_detail_id FROM course_scheduling_detail AS t1 LEFT JOIN grade AS t2 ON t1.grade_id = t2.id ${ew.customSqlSegment}")
    public abstract IPage<RecordCourse> recordGradeLog(IPage<?> iPage, @Param(Constants.WRAPPER) Wrapper wrapper);

    @Select({"<script>",
            "select * from (",
            "SELECT",
            "`t1`.`id` AS `id`,",
            "`t1`.`scheduling_detail_id` AS `scheduling_detail_id`,",
            "`t1`.`lesson_num` AS `lesson_num`,",
            "`t1`.`student_id` AS `student_id`,",
            "`t1`.`student_grade_id` AS `student_grade_id`,",
            "`t1`.`status` AS `status`,",
            "`t1`.`type` AS `type`,",
            "`t1`.`lesson_type` AS `lesson_type`,",
            "`t1`.`course_type` AS `course_type`,",
            "`t1`.`is_repair` AS `is_repair`,",
            "`t1`.`repair_id` AS `repair_id`,",
            "`t1`.`repair_end_date_time` AS `repair_end_date_time`,",
            "`t1`.`status_name` AS `status_name`,",
            "`t1`.`reach_type` AS `reach_type`,",
            "`t1`.`school_id` AS `school_id`,",
            "`t1`.`school_name` AS `school_name`,",
            "`t1`.`old_grade_id` AS `old_grade_id`,",
            "`t1`.`old_teacher_id` AS `old_teacher_id`,",
            "`t1`.`grade_id` AS `grade_id`,",
            "`t1`.`grade_name` AS `grade_name`,",
            "`t1`.`teacher_id` AS `teacher_id`,",
            "`t1`.`teacher_name` AS `teacher_name`,",
            "`t1`.`class_room_id` AS `class_room_id`,",
            "`t1`.`class_room_name` AS `class_room_name`,",
            "`t1`.`course_id` AS `course_id`,",
            "`t1`.`course_name` AS `course_name`,",
            "`t1`.`subjects_id` AS `subjects_id`,",
            "`t1`.`subjects_name` AS `subjects_name`,",
            "`t1`.`old_school_date` AS `old_school_date`,",
            "`t1`.`school_date` AS `school_date`,",
            "`t1`.`time_start` AS `time_start`,",
            "`t1`.`time_end` AS `time_end`,",
            "`t1`.`start_date_time` AS `start_date_time`,",
            "`t1`.`end_date_time` AS `end_date_time`,",
            "`t1`.`order_detail_id` AS `order_detail_id`,",
            "`t1`.`buckle_class_house` AS `buckle_class_house`,",
            "`t1`.`buckle_class_money` AS `buckle_class_money`,",
            "`t1`.`teacher_get_house` AS `teacher_get_house`,",
            "`t1`.`teacher_get_money` AS `teacher_get_money`,",
            "`t1`.`year_and_quarter` AS `year_and_quarter`,",
            "`t1`.`year_part` AS `year_part`,",
            "`t1`.`quarter` AS `quarter`,",
            "`t1`.`is_deduct_house` AS `is_deduct_house`,",
            "`t1`.`is_deduct_money` AS `is_deduct_money`,",
            "`t1`.`no_deduct` AS `no_deduct`,",
            "`t1`.`remark` AS `remark`,",
            "`t1`.`deduct_type` AS `deduct_type`,",
            "`t1`.`created_id` AS `created_id`,",
            "`t1`.`use_leave_num` AS `use_leave_num`,",
            "`t1`.`deleted` AS `deleted`,",
            "`t1`.`created_at` AS `created_at`,",
            "`t1`.`updated_at` AS `updated_at`,",
            "`t1`.`deleted_at` AS `deleted_at`,",
            "`t1`.`check_type` AS `check_type`,",
            "`t1`.`by_record_id` AS `by_record_id`,",
            " t2.name as student_name, ",
            "t3.course_type_id,",
            "t3.year_class_id, ",
            "IF(t4.back_date IS NOT NULL ,t4.back_date ,DATE_ADD( DATE_ADD( t1.school_date, INTERVAL ( 8 - DAYOFWEEK( t1.school_date )) % 7 + 7 DAY ), INTERVAL 1 DAY )) AS back_date, ",
            "WEEKDAY( t1.school_date ) AS week_date ",
            "FROM",
            "record_course AS t1",
            "LEFT JOIN  student t2 ON t1.student_id = t2.id ",
            "LEFT JOIN grade AS t3 ON t1.old_grade_id = t3.id ",
            "LEFT JOIN record_back_date_set AS t4 ON t1.id = t4.record_course_id ",
            ") as t",
            "${ew.customSqlSegment}",
            "</script>"
    })
    public abstract IPage<StudentClassLogRep> studentClassLog(IPage<RecordCourse> iPage, @Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select sum(buckle_class_house) as buckle_class_house,sum(buckle_class_money) as buckle_class_money,sum(teacher_get_house) as teacher_get_house,sum(teacher_get_money) as teacher_get_money ,max(school_date) as school_date from record_course where scheduling_detail_id = #{schedulingDetailId} and is_deduct_house=#{isDeductHouse} and deleted=0")
    public abstract RecordCourse getStatistics(@Param("schedulingDetailId") Long schedulingDetailId, @Param("isDeductHouse") Integer isDeductHouse);

    @Select("select sum(buckle_class_house) as buckle_class_house,sum(buckle_class_money) as buckle_class_money,sum(teacher_get_house) as teacher_get_house,sum(teacher_get_money) as teacher_get_money,max(school_date) as school_date from record_course where scheduling_detail_id = #{schedulingDetailId} and is_deduct_house=#{isDeductHouse} and status=#{status} and deleted=0")
    public abstract RecordCourse getStatisticsSum(@Param("schedulingDetailId") Long schedulingDetailId, @Param("status") Integer status, @Param("isDeductHouse") Integer isDeductHouse);

    @Select("select * from record_course where id = #{id} and deleted=0")
    public abstract RecordCourse select(@Param("id") Long id);

    @Select("select count(*) from record_course where student_id = #{student_id} and grade_id = #{grade_id} and status = 2 and deleted = 0")
    public abstract Integer countLeave(@Param("student_id") Long studentId, @Param("grade_id") Long gradeId);

    @Select("select count(*) from record_course where student_id = #{student_id} and grade_id = #{grade_id} and is_repair = 1 and deleted = 0")
    public abstract Integer countMakeUp(@Param("student_id") Long studentId, @Param("grade_id") Long gradeId);


    @Select("select distinct student_id from record_course ${ew.customSqlSegment}")
    public abstract List<Long> selectStudentId(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);

    @Select("select * from record_course where teacher_id = #{teacher_id}")
    public abstract RecordCourse selectByTeacherId(@Param("teacher_id") Long teacherId);

    @Select("select teacher_id,sum(buckle_class_house) as buckle_class_house,sum(buckle_class_money) as buckle_class_money,teacher_get_house,teacher_get_money from record_course ${ew.customSqlSegment}")
    public abstract IPage<RecordCourse> sumRecordCourse(IPage<?> ipage, @Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select student_id,student_name,sum(buckle_class_house) as buckle_class_house,sum(buckle_class_money) as buckle_class_money from record_course ${ew.customSqlSegment}")
    public abstract IPage<RecordCourse> sumStudentRecordCourse(IPage<?> ipage, @Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("SELECT admin_id FROM role_admin WHERE role_id = 1568160887369994242")
    public abstract List<Long> selectAdminId();

    @Select("SELECT admin_id FROM role_admin WHERE role_id = 1568160769463914497")
    public abstract List<Long> selectAdminCId();

    @Select("SELECT admin_id FROM role_admin WHERE role_id = 1568160915052400642")
    public abstract List<Long> selectAdminZId();


    @Select("SELECT * FROM record_course WHERE (year_part = 2023 AND QUARTER = 2 AND deleted = 0 AND course_type = 3 AND STATUS = 1 ) AND student_id NOT IN (SELECT student_id FROM record_course WHERE (year_part = 2023 AND QUARTER = 2 AND deleted = 0 AND STATUS = 2 AND is_repair = 1 ))")
    public abstract List<RecordCourse> getErrorData();


    /**
     * 获取使用课时
     *
     * @param wrapper
     * @return
     */
    //@Select("select SUM(buckle_class_house) as buckle_class_house from record_course where year_part = 2023 and school_id = 0 and subjects_id = 0 and `quarter` = 1 and school_date BETWEEN 2023-05-22 and 2023-05-23 and deleted = 0 ")
    @Select("select IFNULL(SUM(buckle_class_house),0) as buckle_class_house from record_course ${ew.customSqlSegment} ")
    public abstract Integer getUseCourseCount(@Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     * 从班级获取获取使用课次
     *
     * @param wrapper
     * @return
     */
    @Select("select IFNULL(SUM(use_course_count),0) as use_course_count from student_grade ${ew.customSqlSegment} ")
    public abstract Integer getUseCourseCountForGrade(@Param(Constants.WRAPPER) Wrapper wrapper);


    /**
     * 获取请假课次
     *
     * @param wrapper
     * @return
     */
    @Select("select IFNULL(SUM(buckle_class_house),0) as buckle_class_house from record_course ${ew.customSqlSegment} ")
    public abstract Integer getLeaveCourseCount(@Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     * 获取使用金额
     *
     * @param wrapper
     * @return
     */
    @Select("select IFNULL(SUM(buckle_class_money),0) as buckle_class_money from record_course ${ew.customSqlSegment} ")
    public abstract BigDecimal getRealityPirce(@Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select IFNULL(SUM(buckle_class_house),0) as buckleClassHouse,IFNULL(SUM(buckle_class_money),0) as buckleClassMoney from record_course ${ew.customSqlSegment} ")
    public abstract Map<String, BigDecimal> getRealityPirceAndRealityPirce(@Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     *
     * @param shoolId
     * @param studentId
     * @param subjectsId
     * @param yearPart
     * @param quarterNum
     * @return
     */
    @Select("select count(id) as count from record_course where student_id =${studentId} and subjects_id =${subjectsId} and year_part =${yearPart}  and `quarter` = ${quarterNum} and deleted = 0 and ((course_type = 1 and school_date BETWEEN #{startDate} and #{endDate}) or (course_type = 4 and old_school_date BETWEEN #{startDate} and #{endDate}))")
    public abstract Integer getStudentUpClassCount(@Param("schoolId") Long shoolId, @Param("studentId") Long studentId, @Param("subjectsId") Long subjectsId, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("lessonType") Integer lessonType, @Param("startDate") LocalDate startDate, @Param("endDate")LocalDate endDate);


    @Select("select count(id) as count from record_course where student_id =${studentId} and subjects_id =${subjectsId} and year_part =${yearPart}  and `quarter` = ${quarterNum} and deleted = 0 and lesson_type =${lessonType} and ((course_type = 1 and lesson_num =${lessonNum}) or (course_type = 4 and lesson_num =${lessonNum}))")
    public abstract Integer getStudentUpClassCountForLessonNum(@Param("schoolId") Long shoolId, @Param("studentId") Long studentId, @Param("subjectsId") Long subjectsId, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("lessonType") Integer lessonType, @Param("lessonNum") Integer lessonNum);

    @Select("select IFNULL(MIN(lesson_num),0) as lesson_num_min from record_course where year_part =${yearPart} and `quarter` =${quarterNum} and student_id = ${studentId} and subjects_id =${subjectsId} and lesson_type = ${lessonType} and deleted = 0 and course_type in(1,4)")
    public abstract Integer getMinLessonNum(@Param("schoolId") Long shoolId, @Param("studentId") Long studentId, @Param("subjectsId") Long subjectsId, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("lessonType") Integer lessonType);


    @Select("SELECT id FROM repair_course WHERE school_date=#{schoolDate}")
    List<Long> getIdBySchoolDate(@Param("schoolDate") String schoolDate);


    @Select({
            "<script>",
                "SELECT",
                    "t01.`year_part`,",
                    "t01.`quarter_num`,",
                    "t01.student_id,",
                    "t01.subjects_id,",
                    "t02.lesson_num,",
                    "t02.school_date,",
                    "t02.start_date_time,",
                    "t02.reach_type ,",
                    "t02.status,",
                    "t02.course_type,",
                    "t02.is_repair ",
                "FROM",
                    "(",
                        "SELECT",
                            "* ",
                        "FROM",
                            "student_lesson_num ",
                        "WHERE",
                            "year_part = ${yearPart} ",
                            "AND quarter_num = ${quarterNum} ",
                            "AND student_id = ${studentId} ",
                            "AND lesson_num = ${lessonNum} ",
                            "AND subjects_id = ${subjectsId} ",
                            "AND deleted = 0 ",
                    ") AS t01",
                    "LEFT JOIN (select * from record_course where lesson_type=1 and course_type in(1,4,6))AS t02 ON t01.quarter_num = t02.`quarter` ",
                    "AND t01.lesson_num = t02.lesson_num ",
                    "AND t01.deleted = t02.deleted",
                    "AND t01.student_id = t02.student_id",
                    "AND t01.year_part = t02.year_part",
                    "AND t01.quarter_num = t02.`quarter`",
                    "AND t01.subjects_id = t02.subjects_id",
                    "limit 1",
            "</script>"
    })
    public abstract RecordCourse getStudentClassStatus(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNUm,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("lessonNum") Integer lessonNum);

    @Select({"<script>",
            "select teacher_id,count( id ) AS all_repair_count",
            "from (",
            "SELECT",
            "t02.id,",
            "t02.school_id,",
            "t02.school_name,",
            "t02.teacher_id,",
            "t02.old_teacher_id,",
            "t02.teacher_name,",
            "t02.subjects_id,",
            "t02.school_date",
            "FROM",
            "( SELECT * FROM staff WHERE is_teacher = 1 AND deleted = 0 ) AS t01",
            "LEFT JOIN (",
            "SELECT",
            "* ",
            "FROM",
            "record_course ",
            "WHERE",
            "year_part = ${yearPart} ",
            "AND `quarter` = ${quarterNum} ",
            "AND deleted = 0 ",
            "AND course_type IN ( 3,4,5,6 )",
            ") AS t02 ON t01.id = t02.teacher_id ",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and subjects_id =${subjectsId}",
            "</when>",
            "AND `lesson_type` = 1 ",
            ") AS t",
            "where teacher_id &lt;&gt; old_teacher_id ",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and school_id = ${schoolId} ",
            "</when>",
            "AND teacher_id IN (${teacherStr})",
            "GROUP BY teacher_id",
            "</script>"})
    public abstract List<RepairLesson> getRepairLesson(@Param("schoolId") String schoolId, @Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("subjectsId") String subjectsId, @Param("teacherStr") String teacherStr);


    @Select({"<script>",
            "SELECT teacher_id,SUM( deal_num ) AS dealNum FROM enroll_info WHERE deleted = 0 ",
            "AND add_date BETWEEN '${startDate}' AND '${endDate}' ",
            "AND type = ${type}",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and subjects_id =${subjectsId}",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and school_id = ${schoolId}",
            "</when>",
            "AND teacher_id IN (${teacherStr})",
            "GROUP BY teacher_id",
            "</script>"})
    public abstract List<RepairLesson> enrollmentData(@Param("schoolId") String schoolId, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("subjectsId") String subjectsId, @Param("type") String type, @Param("teacherStr") String teacherStr);


    /**
     * 请假未补的数量,不包含请假已扣金额的数据
     * @param yearPart
     * @param quarterNum
     * @param studentId
     * @param subjectsId
     * @param lessonType
     * @return
     */
    @Select("select count(id) as count from record_course where student_id = ${studentId} and school_id = ${schoolId} and deleted = 0 and year_part = ${yearPart} and `quarter` = ${quarterNum} and lesson_type =${lessonType} and subjects_id =${subjectsId} and `status` =2 and is_repair<>1 and is_deduct_money =0")
    public abstract Integer getLackCount(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("lessonType") Integer lessonType);


    /**
     * 获取到课
     * @param yearPart
     * @param quarterNum
     * @param studentId
     * @return
     */
    @Select("select count(id) as count from record_course where deleted=0 and year_part = ${yearPart} and `quarter` = ${quarterNum} and lesson_type = 1 and `status`=1 and student_id = ${studentId} and subjects_id = ${subjectsId}")
    public abstract Integer getRecordCountArrive(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId);


    @Select({"<script>",
            "SELECT",
                    "SUM( use_leave_num ) AS use_leave_num ",
                    "FROM",
                    "(",
                    "SELECT",
                      "IFNULL( SUM( use_leave_num ), 0 ) use_leave_num ",
                    "FROM",
                      "record_course ",
                    "WHERE",
                      "year_part = ${yearPart} ",
                      "AND `quarter` = ${quarterNum} ",
                      "AND student_id = ${studentId} ",
                      "AND lesson_type = ${lessonType} ",
                      "AND subjects_id = ${subjectsId} ",
                      "AND deleted = 0 UNION ALL",
                    "SELECT",
                      "IFNULL( SUM( use_leave_num ), 0 ) use_leave_num ",
                    "FROM",
                      "repair_course ",
                    "WHERE",
                      "year_part = ${yearPart} ",
                      "AND `quarter` = ${quarterNum} ",
                      "AND student_id = ${studentId} ",
                      "AND lesson_type = ${lessonType} ",
                      "AND subjects_id = ${subjectsId} ",
                      "AND end_date_time > '${dateTimeNow}'",
                      "and course_type =4 ",
                    "AND deleted = 0 ",
                    ") AS t",
            "</script>"})
    public abstract Integer getUseLeaveCount(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("lessonType") Integer lessonType,@Param("dateTimeNow") String dateTimeNow);


    @Select({"<script>",
            "SELECT",
            "* ",
            "FROM",
            "record_course ",
            "WHERE",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND `quarter` = ${quarterNum} ",
            "AND lesson_type = ${lessonType} ",
            "AND `status` = 2 ",
            "AND student_id = ${studentId} ",
            "AND subjects_id = ${subjectsId} ",
            "AND is_repair &lt;&gt;1 ",
            "AND is_deduct_money = 0",
            "ORDER BY school_date asc",
            "limit 1",
            "</script>"})
    public abstract RecordCourse getLeaveData(@Param("yearPart") Integer yearPart,
                                              @Param("quarterNum") Integer quarterNum,
                                              @Param("lessonType") Integer lessonType,
                                              @Param("studentId") Long studentId,
                                              @Param("subjectsId") Long subjectsId);

    @Select({"<script>",
            "SELECT",
            "count(id) as count",
            "FROM",
            "record_course ",
            "WHERE",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND `quarter` = ${quarterNum} ",
            "AND lesson_type = ${lessonType} ",
            "AND `status` = 2 ",
            "AND student_id = ${studentId} ",
            "AND subjects_id = ${subjectsId} ",
            "AND is_repair &lt;&gt;1 ",
            "AND is_deduct_money = 0",
            "</script>"})
    public abstract Integer getLeaveCount(@Param("yearPart") Integer yearPart,
                                              @Param("quarterNum") Integer quarterNum,
                                              @Param("lessonType") Integer lessonType,
                                              @Param("studentId") Long studentId,
                                              @Param("subjectsId") Long subjectsId);

    @Select("UPDATE record_course SET deleted = 1 WHERE id in (${ids}) ")
    public abstract Boolean delLeaveRecord(@Param("ids") String ids);

    /**
     * 精品课 到课率、迟到率，战队和战队详情
     * @return
     */
    @Select({"<script>",
            "SELECT team_id,teacher_id,teacherName,team_name,team_pid,",
            "SUM( student_all ) AS student_all_count,",
            "SUM( arrived ) AS arrived_count,",
            "SUM( `leave` ) AS leave_count,",
            "SUM( `leave` + no_arrived_count_child ) AS no_arrived_count,",
            "SUM(late_count + no_arrived_count_child )  AS tardiness_rate_count ",
            "FROM( ",
            "SELECT",
            "count(t.student_id) AS student_all,",
            "sum( CASE WHEN t.`status` = 1 THEN 1 ELSE 0 END ) AS arrived,",
            "sum( CASE WHEN t.`status` = 2 THEN 1 ELSE 0 END ) AS `leave`,",
            "sum( CASE WHEN t.`reach_type` = 2 OR t.`status` = 2 THEN 1 ELSE 0 END ) AS late_count,",
            "sum( CASE WHEN t.`status` = 0 THEN 1 ELSE 0 END ) AS no_arrived_count_child,",
            "school_id,",
            "getSchoolName ( school_id ) AS school_name,",
            "t1.teacher_id as teacher_id,",
            "sff.`name` AS teacherName,",
            "grade_id,",
            "subjects_id, ",
            "team_id,t1.team_name,t1.team_pid ",
            "FROM",
            "( ",
            "SELECT ",
            "td.*,td.sourse_id as teacher_id ,tm.team_name,tm.id AS team_id,tm.pid AS team_pid ",
            "FROM ",
            "track_market AS tm ",
            "LEFT JOIN track_detail AS td ON tm.id = td.track_id ",
            "AND tm.deleted = td.deleted ",
            "WHERE ",
            "tm.classify = ${classify} ",
            "AND tm.track_type = ${trackType} ",
            "AND tm.`status` = 1 ",
            "AND tm.deleted = 0 ",
            "AND tm.year_part = ${yearPart} ",
            "AND tm.quarter_num = ${quarterNum} ",
            "GROUP BY ",
            "tm.id,td.sourse_id ",
            ") AS t1 ",
            "INNER JOIN (select id,`name` from staff) AS sff ON sff.id = t1.teacher_id",
            "LEFT JOIN (",
            "SELECT school_id,teacher_id,student_id,time_start,time_end,",
            "reach_type,grade_id,subjects_id,year_class_id,",
            "IFNULL(`status`,0) as `status`",
            "FROM (",
            "SELECT",
            "school_id,",
            "old_teacher_id AS teacher_id,",
            "student_id,",
            "`status`,",
            "reach_type,",
            "old_grade_id AS grade_id,",
            "subjects_id,",
            "year_class_id,",
            "time_start,",
            "time_end",
            "FROM",
            "record_course AS t1 ",
            "WHERE",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND `quarter` = ${quarterNum} ",
            "AND `lesson_type` = ${lessonType} ",
            "and old_teacher_id is not null",
            "AND school_date BETWEEN '${startDateLast}' AND '${endDateLast}'",
            "UNION ALL",
            "SELECT",
            "t4.school_id,",
            "t4.teacher_id,",
            "t1.student_id,",
            "t5.`status`,",
            "t5.reach_type,",
            "t1.grade_id,",
            "t2.subjects_id,",
            "t4.year_class_id,",
            "t2.time_start,",
            "t2.time_end",
            "FROM",
            "course_scheduling_detail AS t2",
            "INNER JOIN student_grade AS t1 ON t2.year_part = t1.year_part ",
            "AND t2.`quarter` = t1.quarter_num ",
            "AND t2.deleted = t1.deleted ",
            "AND t2.grade_id = t1.grade_id ",
            "AND t2.lesson_type = t1.lesson_type ",
            "AND t1.reading_status = 1 ",
            "AND t1.`status` = 1",
            "and t2.end_date_time  &gt;= t1.created_at",
            "LEFT JOIN repair_course AS t3 ON t3.out_scheduling_detail_id = t2.id ",
            "AND t3.student_id = t1.student_id",
            "LEFT JOIN grade AS t4 ON t1.grade_id = t4.id",
            "LEFT JOIN record_course AS t5 ON t5.scheduling_detail_id = t2.id ",
            "AND t5.student_id = t1.student_id",
            "AND t5.deleted = 0 ",
            "WHERE",
            "t2.year_part = ${yearPart} ",
            "AND t2.`quarter` = ${quarterNum} ",
            "AND t2.school_date BETWEEN '${startDate}' AND '${endDate}' ",
            "AND t2.lesson_type = ${lessonType} ",
            "AND t2.deleted = 0 ",
            "AND t2.`status` &lt;&gt; 2 ",
            "AND t3.id IS NULL",
            "GROUP BY t1.school_id,t1.subjects_id,t1.student_id,t1.lesson_type",
            "UNION ALL",
            "SELECT",
            "t2.school_id,",
            "t2.old_teacher_id AS teacher_id,",
            "t2.student_id,",
            "t5.`status`,",
            "t5.reach_type,",
            "t2.old_grade_id AS grade_id,",
            "t2.subjects_id,",
            "t2.year_class_id,",
            "t2.time_start,",
            "t2.time_end",
            "FROM",
            "repair_course AS t2",
            "LEFT JOIN record_course AS t5 ON t5.scheduling_detail_id = t2.id ",
            "AND t5.student_id = t2.student_id ",
            "AND t5.deleted = 0 ",
            "WHERE",
            "t2.deleted = 0 ",
            "AND t2.year_part = ${yearPart} ",
            "AND t2.`quarter` = ${quarterNum} ",
            "AND t2.school_date BETWEEN '${startDate}' AND '${endDate}' ",
            "AND t2.`status` &lt;&gt; 2 ",
            "AND t2.lesson_type = ${lessonType} ",
            "and t2.old_teacher_id is not null ",
            ")AS ttt",
            "where 1=1 ",
            "<when test='yearClassIdStr!=null and yearClassIdStr!=&apos;&apos; '>",
            "and ttt.year_class_id in (${yearClassIdStr})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and ttt.subjects_id IN (${subjectsId})",
            "</when>",
            "<when test='timeStart!=null and timeStart!=&apos;&apos; '>",
            "and ttt.time_start &gt;=#{timeStart} ",
            "</when>",
            "<when test='timeEnd!=null and timeEnd!=&apos;&apos; '>",
            "and ttt.time_end &lt;=#{timeEnd} ",
            "</when>",
            ") AS t ON t.teacher_id = t1.teacher_id  ",
            "AND ( find_in_set( t.year_class_id, t1.year_class_id_str ) OR t1.year_class_id_str = '' ) ",
            "AND ( find_in_set( t.school_id, t1.school_id_str ) OR t1.school_id_str = '' ) ",
            "where 1=1 ",
            "<when test='teamId!=null and teamId!=&apos;&apos;'>",
            "AND t1.team_id = ${teamId}",
            "</when>",
            "<when test='name!=null and name!=&apos;&apos; '>",
            "and sff.`name` LIKE '%${name}%'",
            "</when>",
            "GROUP BY t.teacher_id,t1.team_id ",
            ") AS tt2 ",
            "GROUP BY team_id ",
            "<when test='isTeamDetails!=null and isTeamDetails!=&apos;&apos; and isTeamDetails == 1'>",
            ",teacher_id",
            "</when>",
            "</script>"})
    public abstract List<ArrivalRate> arrivalRateTeam(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("subjectsId") String subjectsId, @Param("startDateLast") String startDateLast, @Param("endDateLast") String endDateLast, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("timeStart")String timeStart, @Param("timeEnd")String timeEnd, @Param("lessonType")Integer lessonType, @Param("yearClassIdStr") String yearClassIdStr, @Param("isTeamDetails") String isTeamDetails, @Param("classify") String classify, @Param("trackType") String trackType, @Param("teamId") String teamId, @Param("name") String name);

    /**
     *
     * @param schoolIdStr
     * @param yearPart
     * @param quarterNum
     * @param type
     * @param subjectsId
     * @param startDate
     * @param endDate
     * @param timeStart
     * @param timeEnd
     * @param lessonType
     * @param rateType 1到课率，2迟到率
     * @return
     */
    @Select({"<script>",
            "SELECT *,( leave_count + no_arrived_count_child ) AS no_arrived_count,",
            "( late_count + no_arrived_count_child ) AS tardiness_rate_count",
            "FROM(",
            "SELECT",
            "count(*) AS student_all_count,",
            "sum( CASE WHEN `status` = 1 THEN 1 ELSE 0 END ) AS arrived_count,",
            "sum( CASE WHEN `status` = 2 THEN 1 ELSE 0 END ) AS leave_count,",
            "sum( CASE WHEN `reach_type` = 2 OR STATUS = 2 THEN 1 ELSE 0 END ) AS late_count,",
            "sum( CASE WHEN STATUS IS NULL THEN 1 ELSE 0 END ) AS no_arrived_count_child,",
            "school_id,",
            "getSchoolName ( school_id ) AS school_name,",
            "teacher_id,",
            "teacher_name,",
            "grade_id,",
            "subjects_id ",
            "FROM",
            "(",
            "SELECT",
            "school_id,",
            "old_teacher_id AS teacher_id,",
            "student_id,",
            "`status`,",
            "reach_type,",
            "old_grade_id AS grade_id,",
            "subjects_id,",
            "year_class_id,",
            "time_start,",
            "time_end,",
            "sff0.`name` AS teacher_name",
            "FROM",
            "record_course AS t1 ",
            "INNER JOIN (select id,`name` from staff) AS sff0 ON sff0.id = t1.old_teacher_id",
            "WHERE",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND `quarter` = ${quarterNum} ",
            "AND `lesson_type` = ${lessonType} ",
            "and old_teacher_id is not null",
            "AND school_date BETWEEN '${startDateLast}' AND '${endDateLast}'",
            "<when test='name!=null and name!=&apos;&apos; '>",
            "and sff0.`name` LIKE '%${name}%'",
            "</when>",
            "UNION ALL",
            "SELECT",
            "t4.school_id,",
            "t4.teacher_id,",
            "t1.student_id,",
            "t5.`status`,",
            "t5.reach_type,",
            "t1.grade_id,",
            "t2.subjects_id,",
            "t4.year_class_id,",
            "t2.time_start,",
            "t2.time_end,",
            "sff1.`name` AS teacher_name",
            "FROM",
            "course_scheduling_detail AS t2",
            "INNER JOIN student_grade AS t1 ON t2.year_part = t1.year_part ",
            "AND t2.`quarter` = t1.quarter_num ",
            "AND t2.deleted = t1.deleted ",
            "AND t2.grade_id = t1.grade_id ",
            "AND t2.lesson_type = t1.lesson_type ",
            "AND t1.reading_status = 1 ",
            "AND t1.`status` = 1",
            "and t2.end_date_time  &gt;= t1.created_at",
            "LEFT JOIN repair_course AS t3 ON t3.out_scheduling_detail_id = t2.id ",
            "AND t3.student_id = t1.student_id",
            "LEFT JOIN grade AS t4 ON t1.grade_id = t4.id",
            "INNER JOIN staff AS sff1 ON sff1.id = t4.teacher_id",
            "LEFT JOIN record_course AS t5 ON t5.scheduling_detail_id = t2.id ",
            "AND t5.student_id = t1.student_id",
            "AND t5.deleted = 0 ",
            "WHERE",
            "t2.year_part = ${yearPart} ",
            "AND t2.`quarter` = ${quarterNum} ",
            "AND t2.school_date BETWEEN '${startDate}' AND '${endDate}' ",
            "AND t2.lesson_type = ${lessonType} ",
            "AND t2.deleted = 0 ",
            "AND t2.`status` &lt;&gt; 2 ",
            "AND t3.id IS NULL",
            "<when test='name!=null and name!=&apos;&apos; '>",
            "and sff1.`name` LIKE '%${name}%'",
            "</when>",
            "GROUP BY t1.school_id,t1.subjects_id,t1.student_id,t1.lesson_type",
            "UNION ALL",
            "SELECT",
            "t2.school_id,",
            "t2.old_teacher_id AS teacher_id,",
            "t2.student_id,",
            "t5.`status`,",
            "t5.reach_type,",
            "t2.old_grade_id AS grade_id,",
            "t2.subjects_id,",
            "t2.year_class_id,",
            "t2.time_start,",
            "t2.time_end,",
            "sff.`name` AS teacher_name",
            "FROM",
            "repair_course AS t2",
            "INNER JOIN staff AS sff ON sff.id = t2.old_teacher_id",
            "LEFT JOIN record_course AS t5 ON t5.scheduling_detail_id = t2.id ",
            "AND t5.student_id = t2.student_id ",
            "AND t5.deleted = 0 ",
            "WHERE",
            "t2.deleted = 0 ",
            "AND t2.year_part = ${yearPart} ",
            "AND t2.`quarter` = ${quarterNum} ",
            "AND t2.school_date BETWEEN '${startDate}' AND '${endDate}' ",
            "AND t2.`status` &lt;&gt; 2 ",
            "AND t2.lesson_type = ${lessonType} ",
            "and t2.old_teacher_id is not null ",
            "<when test='name!=null and name!=&apos;&apos; '>",
            "and sff.`name` LIKE '%${name}%'",
            "</when>",
            ") AS t where 1=1",
            "<when test='yearClassIdStr!=null and yearClassIdStr!=&apos;&apos; '>",
            "and year_class_id in (${yearClassIdStr})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and subjects_id IN (${subjectsId})",
            "</when>",
            "<if test= 'type == 3'>",
            "and teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "</if>",
            "<when test= 'schoolId!=null and ((trackId == &apos;&apos; and type ==4) or type != 4)'>",
            "and school_id in(${schoolId})",
            "</when>",
            "<when test='schoolIdStr!=null and schoolIdStr!=&apos;&apos; and schoolId == null and ((trackId == &apos;&apos; and type ==4) or type != 4) '>",
            "and school_id in(${schoolIdStr})",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==1'>",
            "  and teacher_id in(select sourse_id from track_detail where deleted = 0 and track_id =#{trackId})",
            "</when>",
            "<when test='trackId!=null and trackId!=&apos;&apos; and type ==4'>",
            "  and school_id in(select sourse_id from track_detail where deleted = 0 and track_id =#{trackId})",
            "</when>",
            "<when test='timeStart!=null and timeStart!=&apos;&apos; '>",
            "and time_start &gt;=#{timeStart}",
            "</when>",
            "<when test='timeEnd!=null and timeEnd!=&apos;&apos; '>",
            "and time_end &lt;=#{timeEnd}",
            "</when>",
            "GROUP BY",
            "<if test= 'type == 1 or type == 3'>",
            "teacher_id",
            "</if>",
            "<if test= 'type == 2'>",
            "school_id,teacher_id",
            "</if>",
            "<if test= 'type == 4'>",
            "school_id",
            "</if>",
            ") AS tt2",
            "</script>"})
    public abstract List<ArrivalRate> arrivalRate(@Param("schoolIdStr") String schoolIdStr,@Param("yearPart") String yearPart,@Param("quarterNum") String quarterNum,@Param("type") String type,@Param("subjectsId") String subjectsId,@Param("startDateLast") String startDateLast,@Param("endDateLast") String endDateLast,@Param("startDate") String startDate,@Param("endDate") String endDate,@Param("timeStart")String timeStart,@Param("timeEnd")String timeEnd,@Param("lessonType")Integer lessonType,@Param("rateType")Integer rateType,@Param("schoolId") String schoolId,@Param("yearClassIdStr") String yearClassIdStr,@Param("trackId") String trackId,@Param("name") String name);

    /**
     *智能课、刷题课，到课率、准时率，战队列表和战队详情
     */
    @Select({"<script>",
            "SELECT",
            "team_id,teacher_id,teacherName,team_name,",
            "SUM( student_all ) AS student_all_count,",
            "SUM( arrived ) AS arrived_count,",
            "SUM( `leave` ) AS leave_count,",
            "SUM(student_all - arrived ) AS no_arrived_count ,",
            "SUM(temporary_arrived) AS temporary_arrived_count",
            "FROM",
            "(",
            "SELECT",
            "t1.team_name,t1.team_id,t1.team_pid, ",
            "sum( CASE WHEN course_type &lt;&gt; 7 THEN 1 ELSE 0 END ) AS student_all,",
            "sum( CASE WHEN `status` = 1 AND course_type &lt;&gt; 7 THEN 1 ELSE 0 END ) AS arrived,",
            "sum( CASE WHEN `status` = 2 AND course_type &lt;&gt; 7 THEN 1 ELSE 0 END ) AS `leave`,",
            "sum( CASE WHEN course_type = 7 THEN 1 ELSE 0 END ) AS temporary_arrived,",
            "t1.sourse_id AS teacher_id,",
            "sff.`name` AS teacherName ",
            "FROM",
            "( ",
            "SELECT ",
            "td.*,td.sourse_id as teacher_id ,tm.team_name,tm.id AS team_id,tm.pid AS team_pid ",
            "FROM ",
            "track_market AS tm ",
            "LEFT JOIN track_detail AS td ON tm.id = td.track_id ",
            "AND tm.deleted = td.deleted ",
            "WHERE ",
            "tm.classify = ${classify} ",
            "AND tm.track_type = ${trackType} ",
            "AND tm.`status` = 1 ",
            "AND tm.deleted = 0 ",
            "AND tm.year_part = ${yearPart} ",
            "AND tm.quarter_num = ${quarterNum} ",
            "GROUP BY ",
            "tm.id,td.sourse_id ",
            ") AS t1 ",
            "INNER JOIN (select id,`name` from staff) AS sff ON sff.id = t1.sourse_id",
            "INNER JOIN (",
            "SELECT",
            "school_id,",
            "old_teacher_id AS teacher_id,",
            "student_id,",
            "`status`,",
            "reach_type,",
            "old_grade_id AS grade_id,",
            "subjects_id,",
            "year_class_id,",
            "time_start,",
            "time_end,",
            "course_type ",
            "FROM",
            "record_course AS t1 ",
            "WHERE",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND `quarter` = ${quarterNum} ",
            "AND `lesson_type` = ${lessonType} ",
            "AND old_teacher_id IS NOT NULL ",
            "AND ((school_date BETWEEN '${startDateLast}' AND '${endDateLast}' ) ",
            "OR ( school_date BETWEEN '${startDate}' AND '${endDate}' AND course_type = 7 )) ",
            "UNION ALL",
            "SELECT",
            "t4.school_id,",
            "t4.teacher_id,",
            "t1.student_id,",
            "t5.`status`,",
            "t5.reach_type,",
            "t6.grade_id,",
            "t2.subjects_id,",
            "t4.year_class_id,",
            "t2.time_start,",
            "t2.time_end,",
            "1 AS course_type ",
            "FROM",
            "course_scheduling_detail AS t2",
            "INNER JOIN student_grade AS t1 ON t2.year_part = t1.year_part ",
            "AND t2.`quarter` = t1.quarter_num ",
            "AND t2.deleted = t1.deleted ",
            "AND t2.grade_id = t1.grade_id ",
            "AND t2.lesson_type = t1.lesson_type ",
            "AND t1.reading_status = 1 ",
            "AND t1.`status` = 1",
            "INNER JOIN student_grade AS t6 ON t6.year_part = t1.year_part ",
            "AND t6.`quarter_num` = t1.quarter_num ",
            "AND t6.deleted = t1.deleted ",
            "AND t6.subjects_id = t1.subjects_id ",
            "AND t6.`student_id` = t1.student_id ",
            "AND t6.lesson_type = 1 ",
            "AND t6.reading_status = 1 ",
            "AND t6.`status` = 1",
            "and t2.end_date_time &gt;= t6.created_at",
            "LEFT JOIN repair_course AS t3 ON t3.out_scheduling_detail_id = t2.id ",
            "AND t3.student_id = t1.student_id",
            "LEFT JOIN grade AS t4 ON t6.grade_id = t4.id",
            "LEFT JOIN record_course AS t5 ON t5.scheduling_detail_id = t2.id ",
            "AND t5.student_id = t1.student_id ",
            "AND t5.deleted = 0 ",
            "WHERE",
            "t2.year_part = ${yearPart} ",
            "AND t2.`quarter` = ${quarterNum} ",
            "AND t2.school_date BETWEEN '${startDate}' AND '${endDate}' ",
            "AND t2.lesson_type = ${lessonType} ",
            "AND t2.deleted = 0 ",
            "AND t2.`status` &lt;&gt; 2 ",
            "AND t3.id IS NULL ",
            "GROUP BY",
            "t2.id,",
            "t1.school_id,",
            "t1.subjects_id,",
            "t1.student_id,",
            "t1.lesson_type UNION ALL",
            "SELECT",
            "t2.school_id,",
            "t4.teacher_id AS teacher_id,",
            "t2.student_id,",
            "t5.`status`,",
            "t5.reach_type,",
            "t4.id AS grade_id,",
            "t2.subjects_id,",
            "t2.year_class_id,",
            "t2.time_start,",
            "t2.time_end,",
            "1 AS course_type ",
            "FROM",
            "repair_course AS t2",
            "INNER JOIN student_grade AS t6 ON t6.year_part = t2.year_part ",
            "AND t6.`quarter_num` = t2.QUARTER ",
            "AND t6.deleted = t2.deleted ",
            "AND t6.subjects_id = t2.subjects_id ",
            "AND t6.`student_id` = t2.student_id ",
            "AND t6.lesson_type = 1 ",
            "AND t6.reading_status = 1 ",
            "AND t6.`status` = 1",
            "LEFT JOIN grade AS t4 ON t6.grade_id = t4.id",
            "LEFT JOIN record_course AS t5 ON t5.scheduling_detail_id = t2.id ",
            "AND t5.student_id = t2.student_id ",
            "AND t5.deleted = 0 ",
            "WHERE",
            "t2.deleted = 0 ",
            "AND t2.year_part = ${yearPart} ",
            "AND t2.`quarter` = ${quarterNum} ",
            "AND t2.school_date BETWEEN '${startDate}' AND '${endDate}' ",
            "AND t2.`status` &lt;&gt; 2 ",
            "AND t2.lesson_type = ${lessonType} ",
            "AND t2.old_teacher_id IS NOT NULL ",
            ") AS t ON t.teacher_id = t1.sourse_id ",
            "AND ( find_in_set( t.year_class_id, t1.year_class_id_str ) OR t1.year_class_id_str = '' ) ",
            "AND ( find_in_set( t.school_id, t1.school_id_str ) OR t1.school_id_str = '' ) ",
            "WHERE",
            "1=1",
            "<when test='yearClassIdStr!=null and yearClassIdStr!=&apos;&apos; '>",
            "and year_class_id in (${yearClassIdStr})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and subjects_id IN (${subjectsId})",
            "</when>",
            "<when test='timeStart!=null and timeStart!=&apos;&apos; '>",
            "and time_start &gt;=#{timeStart}",
            "</when>",
            "<when test='timeEnd!=null and timeEnd!=&apos;&apos; '>",
            "and time_end &lt;=#{timeEnd}",
            "</when>",
            "<when test='teamId!=null and teamId!=&apos;&apos;'>",
            "t1.team_id = ${teamId}",
            "</when>",
            "<when test='name!=null and name!=&apos;&apos; '>",
            "and sff.`name` LIKE '%${name}%'",
            "</when>",
            "GROUP BY t.teacher_id,t1.team_id ",
            ") AS tt2",
            "GROUP BY team_id",
            "<when test='isTeamDetails!=null and isTeamDetails!=&apos;&apos; and isTeamDetails == 1'>",
            ",teacher_id",
            "</when>",
            "</script>"})
    public abstract List<ArrivalRate> reachRateTeam(@Param("yearPart") String yearPart,@Param("quarterNum") String quarterNum,@Param("subjectsId") String subjectsId,@Param("startDateLast") String startDateLast,@Param("endDateLast") String endDateLast,@Param("startDate") String startDate,@Param("endDate") String endDate,@Param("timeStart")String timeStart,@Param("timeEnd")String timeEnd,@Param("lessonType")Integer lessonType,@Param("yearClassIdStr") String yearClassIdStr,@Param("isTeamDetails") String isTeamDetails,@Param("classify") String classify,@Param("trackType") String trackType,@Param("teamId") String teamId,@Param("name") String name);

    /**
     *
     * @param schoolIdStr
     * @param yearPart
     * @param quarterNum
     * @param type
     * @param subjectsId
     * @param startDate
     * @param endDate
     * @param timeStart
     * @param timeEnd
     * @param lessonType
     * @param radeType 1到课率，2迟到率
     * @param isHistory
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "*,(student_all_count - arrived_count ) AS no_arrived_count ",
            "FROM",
            "(",
            "SELECT",
            "sum( CASE WHEN course_type &lt;&gt; 7 THEN 1 ELSE 0 END ) AS student_all_count,",
            "sum( CASE WHEN `status` = 1 AND course_type &lt;&gt; 7 THEN 1 ELSE 0 END ) AS arrived_count,",
            "sum( CASE WHEN `status` = 2 AND course_type &lt;&gt; 7 THEN 1 ELSE 0 END ) AS leave_count,",
            "sum( CASE WHEN course_type = 7 THEN 1 ELSE 0 END ) AS temporary_arrived_count,",
            "school_id,",
            "getSchoolName ( school_id ) AS school_name,",
            "teacher_id,",
            "teacher_name,",
            "grade_id,",
            "subjects_id ",
            "FROM",
            "(",
            "SELECT",
            "school_id,",
            "old_teacher_id AS teacher_id,",
            "student_id,",
            "`status`,",
            "reach_type,",
            "old_grade_id AS grade_id,",
            "subjects_id,",
            "year_class_id,",
            "time_start,",
            "time_end,",
            "course_type,",
            "sff0.`name` AS teacher_name",
            "FROM",
            "record_course AS t1 ",
            "INNER JOIN (select id,`name` from staff) AS sff0 ON sff0.id = t1.old_teacher_id",
            "WHERE",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND `quarter` = ${quarterNum} ",
            "AND `lesson_type` = ${lessonType} ",
            "AND old_teacher_id IS NOT NULL ",
            "AND ((school_date BETWEEN '${startDateLast}' AND '${endDateLast}' ) ",
            "OR ( school_date BETWEEN '${startDate}' AND '${endDate}' AND course_type = 7 )) ",
            "<when test='name!=null and name!=&apos;&apos; '>",
            "and sff0.`name` LIKE '%${name}%'",
            "</when>",
            "UNION ALL",
            "SELECT",
            "t4.school_id,",
            "t4.teacher_id,",
            "t1.student_id,",
            "t5.`status`,",
            "t5.reach_type,",
            "t6.grade_id,",
            "t2.subjects_id,",
            "t4.year_class_id,",
            "t2.time_start,",
            "t2.time_end,",
            "1 AS course_type,",
            "sff1.`name` AS teacher_name",
            "FROM",
            "course_scheduling_detail AS t2",
            "INNER JOIN student_grade AS t1 ON t2.year_part = t1.year_part ",
            "AND t2.`quarter` = t1.quarter_num ",
            "AND t2.deleted = t1.deleted ",
            "AND t2.grade_id = t1.grade_id ",
            "AND t2.lesson_type = t1.lesson_type ",
            "AND t1.reading_status = 1 ",
            "AND t1.`status` = 1",
            "INNER JOIN student_grade AS t6 ON t6.year_part = t1.year_part ",
            "AND t6.`quarter_num` = t1.quarter_num ",
            "AND t6.deleted = t1.deleted ",
            "AND t6.subjects_id = t1.subjects_id ",
            "AND t6.`student_id` = t1.student_id ",
            "AND t6.lesson_type = 1 ",
            "AND t6.reading_status = 1 ",
            "AND t6.`status` = 1",
            "and t2.end_date_time &gt;= t6.created_at",
            "LEFT JOIN repair_course AS t3 ON t3.out_scheduling_detail_id = t2.id ",
            "AND t3.student_id = t1.student_id",
            "LEFT JOIN grade AS t4 ON t6.grade_id = t4.id",
            "INNER JOIN staff AS sff1 ON sff1.id = t4.teacher_id",
            "LEFT JOIN record_course AS t5 ON t5.scheduling_detail_id = t2.id ",
            "AND t5.student_id = t1.student_id ",
            "AND t5.deleted = 0 ",
            "WHERE",
            "t2.year_part = ${yearPart} ",
            "AND t2.`quarter` = ${quarterNum} ",
            "AND t2.school_date BETWEEN '${startDate}' AND '${endDate}' ",
            "AND t2.lesson_type = ${lessonType} ",
            "AND t2.deleted = 0 ",
            "AND t2.`status` &lt;&gt; 2 ",
            "AND t3.id IS NULL ",
            "<when test='name!=null and name!=&apos;&apos; '>",
            "and sff1.`name` LIKE '%${name}%'",
            "</when>",
            "GROUP BY",
            "t2.id,",
            "t1.school_id,",
            "t1.subjects_id,",
            "t1.student_id,",
            "t1.lesson_type UNION ALL",
            "SELECT",
            "t2.school_id,",
            "t4.teacher_id AS teacher_id,",
            "t2.student_id,",
            "t5.`status`,",
            "t5.reach_type,",
            "t4.id AS grade_id,",
            "t2.subjects_id,",
            "t2.year_class_id,",
            "t2.time_start,",
            "t2.time_end,",
            "1 AS course_type,",
            "sff.`name` AS teacher_name",
            "FROM",
            "repair_course AS t2",
            "INNER JOIN staff AS sff ON sff.id = t2.old_teacher_id",
            "INNER JOIN student_grade AS t6 ON t6.year_part = t2.year_part ",
            "AND t6.`quarter_num` = t2.QUARTER ",
            "AND t6.deleted = t2.deleted ",
            "AND t6.subjects_id = t2.subjects_id ",
            "AND t6.`student_id` = t2.student_id ",
            "AND t6.lesson_type = 1 ",
            "AND t6.reading_status = 1 ",
            "AND t6.`status` = 1",
            "LEFT JOIN grade AS t4 ON t6.grade_id = t4.id",
            "LEFT JOIN record_course AS t5 ON t5.scheduling_detail_id = t2.id ",
            "AND t5.student_id = t2.student_id ",
            "AND t5.deleted = 0 ",
            "WHERE",
            "t2.deleted = 0 ",
            "AND t2.year_part = ${yearPart} ",
            "AND t2.`quarter` = ${quarterNum} ",
            "AND t2.school_date BETWEEN '${startDate}' AND '${endDate}' ",
            "AND t2.`status` &lt;&gt; 2 ",
            "AND t2.lesson_type = ${lessonType} ",
            "AND t2.old_teacher_id IS NOT NULL ",
            "<when test='name!=null and name!=&apos;&apos; '>",
            "and sff.`name` LIKE '%${name}%'",
            "</when>",
            ") AS t ",
            "WHERE",
            "1=1",
            "<when test='yearClassIdStr!=null and yearClassIdStr!=&apos;&apos; '>",
            "and year_class_id in (${yearClassIdStr})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and subjects_id IN (${subjectsId})",
            "</when>",
            "<if test= 'type == 3'>",
            "and teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "</if>",
            "<when test= 'schoolId!=null'>",
            "and school_id in(${schoolId}) ",
            "</when>",
            "<when test='schoolIdStr!=null and schoolIdStr!=&apos;&apos; and schoolId == null '>",
            "and school_id in(${schoolIdStr})",
            "</when>",
            "<when test='timeStart!=null and timeStart!=&apos;&apos; '>",
            "and time_start &gt;=#{timeStart}",
            "</when>",
            "<when test='timeEnd!=null and timeEnd!=&apos;&apos; '>",
            "and time_end &lt;=#{timeEnd}",
            "</when>",
            "GROUP BY",
            "<if test= 'type == 1 or type == 3'>",
            "teacher_id",
            "</if>",
            "<if test= 'type == 2'>",
            "school_id,teacher_id",
            "</if>",
            "<if test= 'type == 4'>",
            "school_id",
            "</if>",
            ") AS tt2",
            "</script>"})
    public abstract List<ArrivalRate> reachRate(@Param("schoolIdStr") String schoolIdStr,@Param("yearPart") String yearPart,@Param("quarterNum") String quarterNum,@Param("type") String type,@Param("subjectsId") String subjectsId,@Param("startDateLast") String startDateLast,@Param("endDateLast") String endDateLast,@Param("startDate") String startDate,@Param("endDate") String endDate,@Param("timeStart")String timeStart,@Param("timeEnd")String timeEnd,@Param("lessonType")Integer lessonType,@Param("radeType")Integer radeType,@Param("isHistory")boolean isHistory,@Param("schoolId") String schoolId,@Param("yearClassIdStr") String yearClassIdStr,@Param("name") String name);

    @Select({"<script>",
            "SELECT ",
            "teacher_id,getStaffNameForId(teacher_id) AS teacher_name,",
            "school_id,getSchoolName(school_id) AS school_name,",
            "SUM( teacher_get_house / 3 ) AS buckleClassHouse,",
            "IFNULL( SUM( teacher_get_money ), 0 ) AS buckleClassMoney ",
            "FROM ",
            "( ",
            "SELECT ",
            "teacher_id,school_id,teacher_get_money,teacher_get_house ",
            "FROM ",
            "record_course ",
            "WHERE ",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND `quarter` = ${quarterNum} ",
            "AND lesson_type = 1 ",
            "AND is_deduct_house = 1 ",
            "AND is_deduct_money = 1 ",
            "AND ((`status` = 1 AND buckle_class_house &gt; 0 ) OR ( `status` = 2 AND is_deduct_house = 1 AND is_deduct_money = 1 AND buckle_class_house &gt; 0 AND deduct_type = 4 )) ",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "AND subjects_id in (${subjectsId}) ",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos;'>",
            "AND teacher_id in (${teacherId}) ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "AND school_id in (${schoolId}) ",
            "</when>",
            "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
            "AND school_date BETWEEN '${startDate}' AND '${endDate}' ",
            "</when>",
            "<if test= 'type == 3'>",
            "AND teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "</if>",
            ") AS t1 ",
            "GROUP BY ",
            "<if test= 'type == 1 or type == 3'>",
            "teacher_id",
            "</if>",
            "<if test= 'type == 2'>",
            "school_id,teacher_id",
            "</if>",
            "<if test= 'type == 4'>",
            "school_id",
            "</if>",
            "ORDER BY ",
            "${isFieldToSc} ",
            "<if test= 'isDescOrAsc == 0'>",
            "ASC",
            "</if>",
            "<if test= 'isDescOrAsc == 1'>",
            "DESC",
            "</if>",
            "</script>"})
    public abstract IPage<ClassesNum> classesNumList(IPage<ClassesNum> page,@Param("yearPart") String yearPart,@Param("quarterNum") String quarterNum,@Param("schoolId") String schoolId,@Param("subjectsId") String subjectsId,@Param("startDate") String startDate,@Param("endDate") String endDate,@Param("teacherId") String teacherId,@Param("type") String type,@Param("isDescOrAsc") String isDescOrAsc,@Param("isFieldToSc") String isFieldToSc);

    @Select({"<script>",
            "SELECT ",
            "school_date,time_start,time_end,student_name,grade_name,subjects_name,course_type,status,IFNULL(( teacher_get_house / 3 ), 0 ) AS buckle_class_house,buckle_class_money,getStaffNameForId(teacher_id) AS teacher_name,getSchoolName(school_id) AS school_name ",
            "FROM ",
            "record_course ",
            "WHERE ",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND `quarter` = ${quarterNum} ",
            "AND lesson_type = 1 ",
            "AND is_deduct_house = 1 ",
            "AND is_deduct_money = 1 ",
            "AND ((`status` = 1 AND buckle_class_house &gt; 0 ) OR ( `status` = 2 AND is_deduct_house = 1 AND is_deduct_money = 1 AND buckle_class_house &gt; 0 AND deduct_type = 4 )) ",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "AND subjects_id in (${subjectsId}) ",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos;'>",
            "AND teacher_id in (${teacherId}) ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "AND school_id in (${schoolId}) ",
            "</when>",
            "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'>",
            "AND school_date BETWEEN '${startDate}' AND '${endDate}' ",
            "</when>",
            "</script>"})
    public abstract IPage<ClassesNum> classesNumDetails(IPage<ClassesNum> page,@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("schoolId") String schoolId, @Param("subjectsId") String subjectsId, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("teacherId") String teacherId);

    @Select("UPDATE record_course SET reach_type = #{reachType} WHERE id = #{id}")
    public abstract Boolean updateReach(@Param("reachType") Integer reachType, @Param("id") Long id);

    @Select({"<script>",
            "SELECT t1.*",
            "FROM ",
            "record_course AS t1 ",
            "INNER JOIN `quarter` AS t2 ON t1.year_part = t2.year_part AND t1.`quarter` = t2.num ",
            "WHERE ",
            "t1.lesson_type = ${lessonType} ",
            "AND t1.student_id = ${studentId} ",
            "AND t1.subjects_id = ${subjectsId} ",
            "AND t1.`status` = 1 ",
            "AND t1.deleted = 0 ",
            "AND t2.id BETWEEN ${startQuarterId} AND ${endQuarterId} ",
            "ORDER BY school_date DESC ",
            "LIMIT 1 ",
            "</script>"})
    public abstract RecordCourse getRecordCourseByLastThreeQuarter(@Param("lessonType") Integer lessonType, @Param("studentId") Long studentId, @Param("subjectsId") String subjectsId, @Param("startQuarterId") Long startQuarterId, @Param("endQuarterId") Long endQuarterId);



    @Select({"<script>",
            "select count(id) AS count  from (SELECT",
            "t1.*",
            "FROM",
            "record_course AS t1",
            "LEFT JOIN cha_study_plan AS t2 ON t1.student_id = t2.student_id ",
            "AND t1.year_part = t2.year_part ",
            "AND t1.`quarter` = t2.quarter_num ",
            "AND t1.lesson_num = t2.lesson_num ",
            "AND t2.lesson_type = ${lessonType} ",
            "AND t2.subjects_id = ${subjectsId} ",
            "AND t2.status = 1 ",
            "and t2.category_id = 1",
            "AND date( t2.created_at ) &lt; #{nowLocalDate}",
            "WHERE",
            "t1.year_part = ${yearPart} ",
            "AND t1.`quarter` = ${quarterNum} ",
            "AND t1.`lesson_num` &lt;= ${tastMaxLessonNum} ",
            "AND t1.lesson_type = 1 ",
            "AND t1.`status` = 1 ",
            "AND t1.`end_date_time` &lt;= #{startDateTime} ",
            "AND t1.subjects_id = ${subjectsId} ",
            "AND t1.deleted = 0 ",
            "AND t2.id IS NULL ",
            "and t1.student_id = ${studentId}",
            "GROUP BY",
            "t1.student_id) as t",
            "</script>"})
    public Integer getNotPassCount(@Param("yearPart") Integer yearPart,
                                   @Param("quarterNum") Integer quarterNum,
                                   @Param("studentId") Long studentId,
                                   @Param("nowLocalDate") String nowLocalDate,
                                   @Param("startDateTime") String startDateTime,
                                   @Param("lessonType") Integer lessonType,
                                   @Param("subjectsId") Long subjectsId,
                                   @Param("tastMaxLessonNum") Integer tastMaxLessonNum
    );

    @Select({"<script>",
            "SELECT",
                      "count( id ) AS count ",
                    "FROM",
                      "(",
                      "SELECT",
                        "t1.id,",
                        "t3.student_id,",
                        "count( * ) AS all_count,",
                        "SUM( CASE WHEN t2.id IS NOT NULL THEN 1 ELSE 0 END ) AS pass_count ",
                      "FROM",
                        "( SELECT * FROM math_db.edu_task WHERE year_part = ${yearPart} AND `quarter_num` = ${quarterNum} AND task_course_num &lt;= ${tastMaxLessonNum} GROUP BY year_class_id, task_course_num ) AS t1",
                        "INNER JOIN math_db.per_student_account AS t3 ON t1.year_part = t3.year_part ",
                        "AND t1.quarter_num = t3.quarter_num ",
                        "AND t1.year_class_id = t3.year_class_id",
                        "LEFT JOIN cha_study_plan AS t2 ON t3.year_part = t2.year_part ",
                        "AND t3.`quarter_num` = t2.quarter_num ",
                        "AND t3.student_id = t2.student_id ",
                        "AND t2.lesson_type = 3 ",
                        "AND t1.task_course_num = t2.lesson_num ",
                        "AND ( t2.subjects_id IN ( ${subjectsId} )) ",
                        "AND t2.created_at  &lt; #{nowLocalDateTime} ",
                        "AND t2.category_id = 1 ",
                      "WHERE",
                        "t1.year_part = ${yearPart} ",
                        "AND t1.`quarter_num` = ${quarterNum} ",
                        "and t3.student_id = ${studentId}",
                      "GROUP BY",
                        "t3.student_id ",
                    "HAVING",
                      "( all_count &gt; pass_count )) AS t",
            "</script>"})
    public Integer getNotPassCountMath(@Param("yearPart") Integer yearPart,
                                         @Param("quarterNum") Integer quarterNum,
                                         @Param("subjectsId") Long subjectsId,
                                         @Param("studentId") Long studentId,
                                         @Param("yearClassId") Integer yearClassId,
                                         @Param("classTypeId") Long classTypeId,
                                         @Param("nowLocalDateTime") String nowLocalDateTime,
                                         @Param("tastMaxLessonNum") Integer tastMaxLessonNum);

    @Select({"<script>",
            "SELECT count(t1.id) count",
            "FROM",
            "<if test ='subjectsId == 2'>",
            "math_db.edu_task AS t1",
            "</if>",
            "<if test ='subjectsId == 3'>",
            "physics_db.edu_task AS t1",
            "</if>",
            "LEFT JOIN cha_study_plan AS t2 ON t1.task_course_num = t2.lesson_num ",
            "AND t1.year_part = t2.year_part ",
            "AND t1.quarter_num = t2.quarter_num ",
            "AND t1.subjects_id = t2.subjects_id ",
            "and t2.student_id = ${studentId}",
            "WHERE",
            "#{nowLocalDate} BETWEEN t1.start_task_date ",
            "AND t1.task_date ",
            "AND t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.deleted = 0 ",
            "AND t1.year_class_id = ${yearClassId} ",
            "AND t1.class_type_id = ${classTypeId} ",
            "AND t1.subjects_id =${subjectsId}",
            "and t2.id is null",
            "</script>"})
    public Integer getNotPassCountMathV2(@Param("yearPart") Integer yearPart,
                                       @Param("quarterNum") Integer quarterNum,
                                       @Param("subjectsId") Long subjectsId,
                                       @Param("studentId") Long studentId,
                                       @Param("yearClassId") Integer yearClassId,
                                       @Param("classTypeId") Long classTypeId,
                                       @Param("nowLocalDate") String nowLocalDate);

    @Select({"<script>",
            "SELECT",
            "GROUP_CONCAT( lesson_num ORDER BY start_date_time) AS lesson_num ",
            "FROM",
            "record_course ",
            "WHERE",
            "year_part = ${yearPart} ",
            "AND `quarter` = ${quarterNum} ",
            "AND lesson_type = ${lessonType} ",
            "AND ((`status` = 1 ) OR ( `status` = 3 ) OR ( `status` = 2 AND is_deduct_house = 1 AND is_deduct_money = 1 )) ",
            "AND subjects_id = ${subjectsId} ",
//            "AND end_date_time &lt;= #{dateTimeNow} ",
            "AND deleted = 0 ",
            "AND student_id = ${studentId} ",
            "AND lesson_num IS NOT NULL ",
            "</script>"})
    public String getYetLessonNumStr(@Param("yearPart") Integer yearPart,
                                     @Param("quarterNum") Integer quarterNum,
                                     @Param("subjectsId") Long subjectsId,
                                     @Param("lessonType") Integer lessonType,
                                     @Param("studentId") Long studentId,
                                     @Param("dateTimeNow") String dateTimeNow
    );
}
